問題描述
帶有 ‑C 選項的 pg_restore 不會創建數據庫 (pg_restore with ‑C option does not create the database)
我正在使用 pg_dump 和 pg_restore 來備份和恢復 postgres 數據庫。
這裡有一些與這個問題相關的文檔信息對於Pg_restore，‑C選項描述如下
‑C
‑ ‑create
在恢復之前創建數據庫。如果還指定了 ‑‑clean，> > 在連接到目標數據庫之前刪除並重新創建它。使用此選項時，以 ‑d 命名的數據庫僅用於發出初始 DROP DATABASE 和 CREATE DATABASE 命令。所有數據都恢復到存檔中出現的數據庫名稱中。
但是，即使我將此選項與 pg_restore 一起使用，我也會收到以下錯誤
pg_restore: [archiver (db)] connection to database "test" failed: FATAL: > database "test" does not exist
根據描述 ‑C 選項應該有創建了丟失的數據庫。但是在我的情況下它不是。
以下是我為備份和恢復所做的步驟：
- 使用pg_dump備份數據庫
pg_dump ‑N backup ‑d test ‑‑format custom ‑v ‑h xxhostxx ‑p 5432 ‑U xxuserxx ‑‑lock‑wait‑timeout 300000 ‑f test_pg_dump.dmp
注意：不使用‑C 選項，因為它僅對純文本格式有意義
已刪除 test 數據庫
使用pg_resore恢復數據庫
pg_restore ‑C ‑d test ‑v ‑h xxhostxx ‑p 5432 ‑U xxuserxx test_pg_dump.dmp**
我不明白這裡有什麼問題！我做錯什麼了嗎？如果需要更多信息，請告訴我。
參考解法
方法 1:
Exactly like @Eelke said ‑ you've got in file wrote 'create database' so this database does not exist when you're running script... That's what for there is always 'postgres' database. Try this:
pg_restore ‑C ‑d postgres ‑v ‑h xxhostxx ‑p 5432 ‑U xxuserxx test_pg_dump.dmp**
And this should:
- connect to postgres database
- Create test database
- Disconnect from postgres and connect to test
- Upload data into database
Of course check who is owner of postgres database ‑ in most cases you have to run this as user 'postgres'.
方法 2:
It never worked for me so this command creates the database
createdb ‑h HOST ‑U USER ‑W DB_NAME
then execute the pg restore
pg_restore ‑d DB_NAME ‑v ‑h HOST ‑p PORT ‑U USER DUMP_FILE.dump**
End of story
方法 3:
The following quote doesn't mean what you might think it means. I also had to read it thrice before realizing what they were saying.
When this option is used, the database named with ‑d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.
It means that pg_restore will initially connect to the database specified with ‑d. It will NOT create that database. It creates a database with the name from the archive you are restoring and restores the data into that database.
方法 4:
From man pg_restore EXAMPLES section
Assume we have dumped a database called mydb into a custom‑format dump file:
$ pg_dump ‑Fc mydb > db.dump
To drop the database and recreate it from the dump:
$ dropdb mydb
$ pg_restore ‑C ‑d postgres db.dump
The database named in the ‑d switch can be any database existing in the cluster; pg_restore only uses it to issue the CREATE DATABASE command for mydb.
With ‑C, data is always restored into the database name that appears in the dump file.
To reload the dump into a new database called newdb:
$ createdb ‑T template0 newdb
$ pg_restore ‑d newdb db.dump
Notice we don't use ‑C, and instead connect directly to the database to be restored into. Also note that we clone the new database from template0 not
template1, to ensure it is initially empty.
Thus in your case:
$ createdb ‑h xxhostxx ‑p 5432 ‑U xxuserxx ‑T template0 test
$ pg_restore ‑h xxhostxx ‑p 5432 ‑U xxuserxx ‑d test db.dump
(by Swapnil17、Jendrusk、Andre Leon Rangel、Eelke、borracciaBlu)